package chapter4.comJDBC;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;

import chapter4.comJDBC.User;

public class UserDao {
	private JdbcTemplate jdbcTemplate;
	private static String tablename = "student";
	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}
	/*execute()方法*/
	public void executeTest() {
		String sql = "CREATE TABLE temp(user_id integer, name varchar(100))";
		jdbcTemplate.execute(sql);
	}
	/*update()方法执行insert插入操作*/
	public void updateInsert(User user){
		String sql = String.format("insert into %s values(?,?,?)", tablename);
		jdbcTemplate.update(sql,
				new Object[]{user.getId(),user.getName(),user.getAge()});
	}	

	/*update()方法执行update修改操作*/
	public void updateEdit(String id){
		String sql = String.format("update %s set age=50 where id=?", tablename);
		jdbcTemplate.update(sql,new Object[]{id});
	}

	/*update()方法执行delete删除操作*/
	public void updateDel(){
		String sql = String.format("delete from %s where id=7", tablename);
		jdbcTemplate.update(sql);
	}
	

	
	/*queryForObject()方法*/
	public int findNumbers() {
		String sql = String.format("SELECT COUNT(*) FROM %s", tablename);
		int total = jdbcTemplate.queryForObject(sql,Integer.class);
		return total;
	}
	
	/*queryForObject()方法，绑定参数*/
	public String findNameById(int id) {
		String sql = String.format("SELECT NAME FROM %s WHERE id = ?", tablename);
		String name = (String) jdbcTemplate.queryForObject(sql, new Object[] { id }, String.class);
		return name;
	}
	/*queryForList()方法*/
	public List<Map<String,Object>> findAllMap(){
		String sql = String.format("SELECT * FROM %s", tablename);
		return jdbcTemplate.queryForList(sql);
	}	
	
	/*queryForList()方法,绑定参数*/
	public List<Map<String,Object>> getList(String name){
		String sql = String.format("SELECT * FROM %s where name like ?", tablename);
		return jdbcTemplate.queryForList(sql,new Object[] {"%"+name+"%"});
	}
	
	/*queryForMap()方法*/
	public Map<String,Object> findMapById(int id){
		String sql = String.format("SELECT * FROM %s WHERE id = ?", tablename);
		return jdbcTemplate.queryForMap(sql,new Object[]{id});
	}
	

}
